#!/usr/bin/python
from hashdb_output import log
import sqlite3 as sql
from hashdb_config_base import CombineDB
from collections import namedtuple
from hashdb_mntent_wrapper import truepath
import os
import re

def count_components(path):
    '''Returns the number of components in the given path'''
    return path.count('/')

import hashdb_sql
class HashDatabase2(object):
    AttachLocal  = namedtuple('AttachLocal', 'name database grafts')
    AttachRemote = namedtuple('AttachRemote', 'name database subtrees')

    RowDetailed = namedtuple('RowDetailed', 'path time size hash mark tab src is_remote')
    RowBasic    = namedtuple('RowBasic', 'path time size hash mark')

    def __init__(self, filename, marking=False):
        object.__init__(self)

        self._filename   = filename
        self._mark       = None
        self._connection = None
        self._marking    = marking

        self._locals  = []
        self._remotes = []

    @property
    def filename(self):
        return self._filename
    @property
    def marking(self):
        return self._marking
    @marking.setter
    def marking(self, value):
        self._marking = value
    @property
    def locals(self):
        return self._locals
    @property
    def remotes(self):
        return self._remotes
    @property
    def connection(self):
        return self._connection
    @property
    def mark(self):
        if self._mark == None:
            with self.connection as connection:
                self._mark = connection.execute("INSERT INTO marktab (time) VALUES (strftime('%s','now'))").lastrowid
        return self._mark

    def get_local_by_name(self, name):
        for local in self.locals:
            if local.name == name:
                return local
    def get_local_by_path(self, path):
        path = truepath(path)
        for local in self.locals:
            if local.database == path:
                return local

    def get_remote_by_name(self, name):
        for remote in self.remote:
            if remote.name == name:
                return remote
    def get_remote_by_path(self, path):
        path = truepath(path)
        for remote in self.remote:
            if remote.database == path:
                return remote

    def _subtrees_add(self, subtrees, subtree):
        if (None in subtrees) or (subtree in subtrees):
            # we are already asking for the entire tree / we are already contained
            pass
        elif subtree == None:
            # ask for the entire tree
            subtrees.clear()
            subtrees.add(None)
        else:
            # check we are not already contained by another subtree
            for x in subtrees:
                if subtree.startswith(x + '/'):
                    # subtree already specified via a larger selection
                    break
            else:
                # check to see if we superceed any existing subtrees
                remove = set()
                for x in subtrees:
                    if x.startswith(subtree + '/'):
                        remove.add(x)

                subtrees.difference_update(remove)
                subtrees.add(x)

    def add_local(self, database, target, subtree):
        database = truepath(database)
        target   = hashdb_sql.normalize(truepath(target)) if target not in ['//' or '\\\\'] else None
        if target in ['/', '//']:
            target = None

        subtree  = hashdb_sql.normalize(truepath(subtree)) if subtree not in ['//' or '\\\\'] else None
        if subtree in ['/', '//']:
            subtree = None

        data = self.get_local_by_path(database)
        if data == None:
            data = HashDatabase2.AttachLocal('localdb%02d' % len(self.locals), database, {})
            self._locals.append(data)
        self._subtrees_add(data.grafts.setdefault(target, set()), subtree)

    def extend_locals(self, databases):
        for local in databases:
            self.add_local(database=local.database, target=local.target, subtree=local.subtree)

    def add_remote(self, database, subtree):
        database = truepath(database)
        subtree  = hashdb_sql.normalize(truepath(subtree)) if subtree not in ['//' or '\\\\'] else None
        if subtree == '/':
            subtree = None

        data = self.get_remote_by_path(database)
        if data == None:
            data = HashDatabase2.AttachRemote('remotedb%02d' % len(self._remotes), database, set())
        self._subtrees_add(data.subtress, subtree)


    def extend_remotes(self, databases):
        for remote in databases:
            self.add_remote(database=remote.database, subtree=remote.subtree)

    def open(self):
        # Open connection / Create database
        try:
            connection = hashdb_sql.connect(self._filename)
        except OSError, ex:
            log.error('error: Unable to open primary database (%s): %s' % (self._filename, ex))
            return False
        except sql.Error, ex:
            log.error('error: Unable to open primary database (%s): %s' % (self._filename, ex))
            return False

        # Create schema (if it doesn't already exist)
        try:
            with connection:
                connection.executescript(r"""
                    PRAGMA foreign_keys = ON;

                    CREATE TABLE IF NOT EXISTS marktab (
                        mark INTEGER PRIMARY KEY AUTOINCREMENT,
                        time LONG
                    );

                    INSERT OR IGNORE INTO marktab (mark, time) VALUES (0, strftime('%s', 'now'));

                    CREATE TABLE IF NOT EXISTS hashtab (
                        path TEXT PRIMARY KEY NOT NULL,
                        hash TEXT NOT NULL,
                        size LONG NOT NULL,
                        time LONG NOT NULL,
                        mark INTEGER NOT NULL,
                        FOREIGN KEY(mark) REFERENCES marktab(mark)
                    );

                    CREATE INDEX IF NOT EXISTS hashtab_by_path ON hashtab (
                        path
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_hash ON hashtab (
                        hash
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_size ON hashtab (
                        size
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_hash_size ON hashtab (
                        hash,
                        size
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_size_time ON hashtab (
                        size,
                        time
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_hash_size_time ON hashtab (
                        hash,
                        size,
                        time
                    );
                    CREATE INDEX IF NOT EXISTS hashtab_by_mark ON hashtab (
                        mark
                    );
                """)
        except sql.Error, ex:
            log.error('error: Unable to create schema: %s' % ex)
            return False

        # Attach local databases
        for local in self.locals:
            try:
                with connection:
                    connection.execute('ATTACH ? AS %s' % local.name, (local.database,))
            except OSError, ex:
                log.error('error: Unable to attach local database (%r): %s' % (local.database, ex))
                return False
            except sql.Error, ex:
                log.error('error: Unable to attach local database (%r): %s' % (local.database, ex))
                return False

        # Attach local databases
        for remote in enumerate(self.remotes):
            try:
                with connection:
                    connection.execute('ATTACH ? AS %s' % remote.name, (remote.database,))
            except OSError, ex:
                log.error('error: Unable to attach remote database (%r): %s' % (remote.database, ex))
                return False
            except sql.Error, ex:
                log.error('error: Unable to attach remote database (%r): %s' % (remote.database, ex))
                return False

        self._connection = connection
        return True

    def build_orderby(self, order=[]):
        order = self.build_order(order)
        if order == None:
            return ''
        return '\nORDER BY ' + order
    def build_order(self, order=[]):
        if len(order) == 0:
            return None
        return ',\n      '.join(order)
    def build_where(self, andfilters=[], orfilters=[]):
        orfilter = self.build_or_filter(orfilters)
        if orfilter != None:
            andfilters = orfilter + andfilters
        andfilter = self.build_and_filter(andfilters)

        if andfilter == None:
            return ''
        return '\nWHERE ' + andfilter

    def build_and_filter(self, filters):
        if len(filters) == 0:
            return None
        return '\nAND   '.join('(' + f + ')' for f in filters)
    def build_or_filter(self, filters):
        if len(filters) == 0:
            return None
        return '\nOR    '.join('(' + f + ')' for f in filters)

    def build_select(self, columns, aliases, andfilters=[], orfilters=[]):
        if (columns == None) or len(columns) == 0:
            columns = ['path', 'size', 'time', 'hash', 'mark', 'tab', 'src']

        aliases2 = {
            'path'  : "path",
            'size'  : "size",
            'time'  : "time",
            'hash'  : "hash",
            'mark'  : "mark",
            'tab'   : "'%s'" % aliases['table'],
            'src'   : "'<unspecified>'",
            'is_remote': "0"
        }
        aliases2.update(aliases)

        select = ''.join([
            'SELECT ',
            ',\n       '.join(('(%%(%s)s) as %s' % (c,c)) for c in columns),
            '\nFROM   %(table)s',
            self.build_where(andfilters, orfilters)
        ]) % aliases2

        return select

    def build_selects_primary(self, columns=[], filters=[]):
        aliases = {
            'table' : "hashtab",
            'src'   : "'<primary>'"
        }
        select = self.build_select(columns, aliases, filters)
        yield (select, {})

    def build_selects_local(self, local, columns=[], filters=[]):
        paramaters = {}
        for i, (target, subtrees) in enumerate(local.grafts.iteritems()):
            aliases = {
                'target': ":_%s_target%02d" % (local.name, i)
            }
            paramaters[aliases['target']] = target if target else ''
            aliases.update({
                'path'  : ("%(target)s || path" % aliases) if target else "path",
                'mark'  : "0",
                'table' : "%s.hashtab" % local.name,
                'src'   : "'%s'" % local.name,
            })

            if None in subtrees:
                # select the whole thing
                yield (self.build_select(columns, aliases, filters), paramaters)
            else:
                for j,subtree in enumerate(subtrees):
                    aliases.update({
                        'subtree': ":_%s_subtree%02d_%02d" % (local.name, i, j)
                    })
                    paramaters[aliases['subtree']] = subtree
                    aliases.update({
                        'path': ((":%(target)s || " if target else "") + "substr(path, 1, length(%(subtree)s))") % aliases
                    })
                    yield (
                        self.build_select(
                            columns,
                            aliases,
                            filters,
                            [
                                "%(path)s = %(subtree)s",
                                "substr(%(path)s, 1, length(%(subtree)s) + 1) = %(subtree)s || '/'"
                            ]
                        ),
                        paramaters
                    )

    def build_selects_remote(self, remote, columns=[], filters=[]):
        paramaters = {}

        paramaters[aliases['target']] = target
        aliases.update({
            'mark'  : "0",
            'table' : "%s.hashtab" % remote.name,
            'src'   : "'%s'" % remote.name,
            'is_remote': "1"
        })

        orfilters = []
        for i, subtree in enumerate(subtrees):
            subtree_name = '_%s_subtree%02d' % (remote.name, i)
            paramaters[subtree_name] = subtree
            orfilters.append("%%(path)s = :%s" % subtree_name)
            orfilters.append("substr(%%(path)s, 1, length(:%s) + 1) = :%s || '/'" % (subtree_name, subtree_name))

        select = self.build_select(columns, aliases, filters, orfilters)
        yield select, paramaters

    def build_selects(self, sources=[None], columns=[], filters=[]):
        for source in sources:
            if source == None:
                found = self.build_selects_primary(columns, filters)
            elif isinstance(source, list):
                found = self.build_selects(source, columns, filters)
            elif isinstance(source, HashDatabase2.AttachLocal):
                found = self.build_selects_local(source, columns, filters)
            elif isinstance(source, HashDatabase2.AttachRemote):
                found = self.build_selects_remote(source, columns, filters)
            elif isinstance(source, basestring):
                source = truepath(source)
                local = self.get_local_by_path(source)
                if local != None:
                    found = self.build_selects_local(local, columns, filters)
                else:
                    remote = self.get_remote_by_path(source)
                    if remote != None:
                        found = self.build_selects_remote(remote, columns, filters)
                    else:
                        log.error('error: unknown database %r' % source)
                        continue
            else:
                raise ValueError, 'invalid source specified in sources list'

            for result in found:
                yield result

    def build_union(self, sources=[None], columns=[], filters=[]):
        selects = []
        allparamaters = {}
        for select, paramaters in self.build_selects(sources, columns, filters):
            selects.append(select)
            allparamaters.update(paramaters)
        return ('\nUNION\n'.join(selects), allparamaters)
    def select_union(self, sources=[None], columns=[], filters=[], orderby=[]):
        select, paramaters = self.build_union(sources, columns, filters)
        return self.connection.execute(select + self.build_orderby(orderby), paramaters)

    def path_hash(self, path, stat):
        '''Returns: hash of the file specified by @path if available, else None
        If hash is available and marking is True, marks the path.
        Seeks hash information from all local databases (but not remote ones)
        '''

        path = hashdb_sql.normalize(path)

        # check primary table
        row = self.connection.execute('SELECT mark, hash, time, size FROM hashtab WHERE path=?', (path,)).fetchone()
        if row:
            # database is already up to date?
            if row['mark'] == self.mark:
                return row['hash']

            # database contains a match, but it isn't yet tagged
            if (row['time'] == stat.st_mtime) and (row['size'] == stat.st_size):
                if self.marking:
                    self.connection.execute('UPDATE hashtab SET mark=? WHERE (path=?) AND (time=?) AND (size=?)', (self.mark, path, stat.st_mtime, stat.st_size))
                return row['hash']

        # check attached local databases
        for local in self.locals:
            for select, paramaters in self.build_selects_local(local, ['hash'], ["%(path)s = :target_path", "%(time)s = :target_time", "%(size)s = :target_size"]):
                paramaters['target_path'] = path
                paramaters['target_time'] = stat.st_mtime
                paramaters['target_size'] = stat.st_size

                #log.debug(select)
                #log.debug(paramaters)
                row = self.connection.execute(select, paramaters).fetchone()
                if row:
                    self.path_setstat(path, stat, row['hash'])
                    return row['hash']

        # nothing found
        return None

    def path_setstat(self, path, stat, hash):
        '''inserts/updates @path in the database with information from @stat and @hash'''
        return self.path_setdirect(path, stat.st_mtime, stat.st_size, hash)

    def path_setdirect(self, path, time, size, hash):
        '''inserts/updates @path in the database'''
        path = hashdb_sql.normalize(path)
        return self.connection.execute('INSERT OR REPLACE INTO hashtab (path, time, size, hash, mark) VALUES (?,?,?,?,?)', (path, time, size, hash, self.mark)).rowcount

    def path_get_prime(self, path):
        path = hashdb_sql.normalize(path)
        row = self.connection.execute('SELECT * FROM hashtab WHERE path=?', (path,)).fetchone()
        if row:
            return HashDatabase2.RowBasic(**row)
        return None

    def path_select_duplicates(self, path, hash, size):
        path = hashdb_sql.normalize(path)
        for select, paramaters in self.build_selects(
                    sources=[None, self.locals, self.remotes],
                    columns=HashDatabase2.RowDetailed._fields,
                    filters=[
                        'hash = :hash',
                        'size = :size',
                        'path <> :path',
                    ]
                ):
            paramaters['path'] = path
            paramaters['size'] = size
            paramaters['hash'] = hash

            log.debug('-- select')
            log.debug(select)
            log.debug('-- paramaters')
            log.debug(paramaters)

            for row in self.connection.execute(select, paramaters):
                yield HashDatabase2.RowDetailed(**row)

    def path_select_subtrees(self, targets=['//'], filters=[], paramaters={}, depth=True):
        alltargets = {}
        for target in targets:
            target_tables, target_path = self.target_split(target)
            target_path = hashdb_sql.normalize(target_path)
            for target_type, target_index, target_table in target_tables:
                alltargets.setdefault((target_type, target_index, target_table), []).append(target_path)

        dborder = list(alltargets.keys())
        dborder.sort(key=lambda x: x[2])

        allselects = []
        allparamaters = paramaters.copy()
        for table_index, target in enumerate(order):
            target_type, target_index, target_table = target
            target_paths = alltargets[target]

            aliases = { 'table': target_table }

            if target_type == 'prime':
                aliases['mark'] = "mark"
                aliases['root'] = "'<prime>'"
                aliases['full'] = "'<prime>:' || path"
                aliases['user'] = "path"
            elif target_type == 'local':
                aliases['mark'] = "0"
                aliases['root'] = "'<local>'"
                aliases['full'] = "'<local>:' || path"
                aliases['user'] = "path"
            elif target_type == 'remote':
                allparamaters['_dbalias%02d' % target_index] = self.remote_fnalias(target_index)
                aliases['mark'] = "0"
                aliases['root'] = ":_dbalias%02d || ':' || path" % target_index
                aliases['full'] = ":_dbalias%02d || ':' || path" % target_index
                aliases['user'] = ":_dbalias%02d || ':' || path" % target_index

            select = """
                SELECT path,
                       time,
                       size,
                       hash,
                       %(mark)s AS mark,
                       %(root)s AS root,
                       %(full)s AS full,
                       %(user)s AS user
                FROM   %(table)s
                WHERE
            """ % aliases

            pathfilters = []
            if ('//' not in target_paths) and ('/' not in target_paths):
                for i, target_path in enumerate(target_paths):
                    name = '_target%02d_%02d' % (table_index, i)
                    allparamaters[name] = target_path
                    pathfilters.append("(path = :%(name)s)" % name)
                    pathfilters.append("(substr(path, 1, length(:%(name)s) + 1) = path || '/')")

            select += ' AND '.join(
                ['1']
                ['(' + ' OR '.join(pathfilters) + ')'] +\
                ['(' + f + ')' for f in filters]
            )

            allselects.append(select)


        select = ' UNION '.join(allselects) + ' ORDER BY ' + ('mark DESC, full' if depth else 'mark DESC, path_count(path), path')
        for row in self.connection.execute(select, allparamaters):
            yield self.RowDetailed(**row)


class HashDatabase(object):
    '''Maintains access to a hashdb database and provides all required database functionality.'''

    def __init__(self, filename):
        object.__init__(self)

        self._filename = filename
        self._combines = []
        self._mark = None
        self._conn = None

    def add_combine(self, combine):
        self._combines.append(combine)
    def add_combines(self, combines):
        self._combines.extend(combines)

    @property
    def connection(self):
        return self._conn

    def open(self):
        # Open primary database
        try:
            conn = sql.connect(self._filename)
            conn.row_factory = sql.Row
            conn.text_factory = str
            conn.create_function('count_components', 1, count_components)
            self._create_schema(conn)
        except OSError, ex:
            log.error('error: Unable to open primary database (%s): %s' % (self._filename, ex))
            return False
        except sql.Error, ex:
            log.error('error: Unable to open primary database (%s): %s' % (self._filename, ex))
            return False

        # Attach secondary databases
        for i, combine in enumerate(self._combines):
            try:
                alias = self._dbalias(i)
                with conn:
                    conn.execute(
                        r"""ATTACH ? as %s""" % alias,
                        (combine.database,)
                    )
            except OSError, ex:
                log.error('error: Unable to open secondary database (%s): %s' % (combine.database, ex))
                return False
            except sql.Error, ex:
                log.error('error: Unable to open secondary database (%s): %s' % (combine.database, ex))
                return False

        # Create combined view
        try:
            self._create_combined(conn)
        except sql.Error, ex:
            log.error('error: Unable to create combined database view: %s' % ex)
            return False

        # Create working tables
        try:
            self._create_working(conn)
        except sql.Error, ex:
            log.error('error: Unable to create working tables: %s' % ex)
            return False

        '''
        import time
        import random
        with conn:
            conn.execute('INSERT OR REPLACE INTO hashtab (path, hash, size, time, mark) VALUES (?, ?, ?, ?, ?)', ('/test', '1234', 1234, random.randint(1,1000), time.time()))
        '''

        self._conn = conn
        return True

    def _create_basic_schema(self, conn, name, temporary=False):
        primary  =  'NOT NULL' if temporary else 'PRIMARY KEY ASC'
        temporary = 'TEMPORARY' if temporary else ''

        with conn:
            conn.executescript(r"""
                CREATE %(temporary)s TABLE IF NOT EXISTS %(name)s (
                    path TEXT %(primary)s,
                    hash TEXT NOT NULL,
                    size LONG NOT NULL,
                    time LONG NOT NULL,
                    mark LONG NOT NULL
                );

                CREATE INDEX IF NOT EXISTS %(name)s_by_path ON %(name)s (
                    path
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_hash ON %(name)s (
                    hash
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_size ON %(name)s (
                    size
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_hash_size ON %(name)s (
                    hash,
                    size
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_size_time ON %(name)s (
                    size,
                    time
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_hash_size_time ON %(name)s (
                    hash,
                    size,
                    time
                );
                CREATE INDEX IF NOT EXISTS %(name)s_by_mark ON %(name)s (
                    mark
                );
            """ % { 'temporary':temporary, 'primary':primary, 'name':name })

    def _dbalias(self, i):
        return 'remotedb%02d' % i

    def _create_combined(self, conn):
        self._create_basic_schema(conn, 'combinedtab', True)

        selects = []
        argmap  = {}

        selects.append(r"""
            SELECT
                path,
                hash,
                size,
                time,
                mark
            FROM
                hashtab
        """)

        for i, combine in enumerate(self._combines):
            local = combine.local
            if local != None:
                local = os.path.abspath(local)
            else:
                local = '/'
            remote = combine.remote
            if remote == None:
                remote = '/'

            if (local == '/') and (remote == '/'):
                selects.append(r"""
                    SELECT
                        path,
                        hash,
                        size,
                        time,
                        0 as mark
                    FROM
                        %s.hashtab
                """ % self._dbalias(i))
            else:
                selects.append(r"""
                    SELECT
                        :%(name)s_local || substr(path, :%(name)s_remote_len) as path,
                        hash,
                        size,
                        time,
                        0 as mark
                    FROM
                        %(name)s.hashtab
                    WHERE
                        (path = :%(name)s_remote) OR
                        (substr(path, 1, :%(name)s_remote_len + 1) = :%(name)s_remote || '/')
                """ % {'name': self._dbalias(i)})

                argmap.update({
                    '%s_local'      % self._dbalias(i): local,
                    '%s_remote'     % self._dbalias(i): remote,
                    '%s_remote_len' % self._dbalias(i): len(remote),
                })

        statement = r"""
            INSERT OR IGNORE INTO combinedtab
        """ +\
        r"""
            UNION
        """.join(selects) +\
        r"""
            ORDER BY
                path,
                mark DESC
        """

        with conn:
            conn.execute(statement, argmap)

    def _create_working(self, conn):
        with conn:
            conn.executescript(r"""
                CREATE TEMPORARY TABLE IF NOT EXISTS dirtab (
                    path TEXT PRIMARY KEY
                );
            """)

    def _create_schema(self, conn):
        self._create_basic_schema(conn, 'hashtab', False)

        with conn:
            conn.executescript(r"""
                CREATE TABLE IF NOT EXISTS marktab (
                    mark INTEGER PRIMARY KEY AUTOINCREMENT,
                    time LONG
                );
            """)

    @property
    def mark(self):
        if self._mark == None:
            with self._conn:
                self._mark = self._conn.execute(r"""INSERT INTO marktab (time) VALUES (strftime('%s','now'))""").lastrowid
        return self._mark

    def _ismatch(self, row, stat):
        return (row['time'] == stat.st_mtime) and (row['size'] == stat.st_size)

    def path_mark(self, path):
        with self._conn as conn:
            conn.execute(r"""UPDATE hashtab SET mark=? WHERE path=?""", (self.mark, path))
    def path_update(self, path, stat, hash):
        self.path_update_direct(path, stat.st_size, stat.st_mtime, hash)
    def path_update_direct(self, path, size, time, hash):
        with self._conn as conn:
            conn.execute(r"""UPDATE hashtab SET hash=?, size=?, time=?, mark=? WHERE path=?""", (hash, size, time, self.mark, path))

    def path_dirdone(self, path):
        row = self._conn.execute(r"""SELECT * FROM dirtab WHERE path=?""", (path,)).fetchone()
        if not row:
            with self._conn as conn:
                conn.execute(r"""INSERT INTO dirtab (path) VALUES (?)""", (path,))
            return False
        else:
            return True

    def path_hash(self, path, stat):
        row = self._conn.execute(r"""SELECT * FROM hashtab WHERE path=?""", (path,)).fetchone()
        if row:
            if row['mark'] == self.mark:
                return row['hash'] # already upto date
            if self._ismatch(row, stat):
                self.path_mark(path)
                return row['hash'] # old hash is still valid

        for row in self._conn.execute(r"""SELECT * FROM combinedtab WHERE path=?""", (path,)):
            if self._ismatch(row, stat):
                self.path_update_direct(path=path, hash=row['hash'], size=row['size'], time=row['time'])
                return row['hash'] # hash from combined database is valid

        return None

    def path_discard_unmarked(self, path):
        ##self._conn.execute(r"""DELETE FROM hashtab WHERE mark=?""",  (self.mark,))
        pass

    def path_insert(self, path, stat, hash):
        with self._conn as conn:
            conn.execute(r"""INSERT OR REPLACE INTO hashtab (path, hash, size, time, mark) VALUES (?, ?, ?, ?, ?)""", (path, hash, stat.st_size, stat.st_mtime, self.mark))

    def path_findall(self, path):
        for row in self._conn.execute(r"""SELECT * FROM hashtab WHERE path=? UNION SELECT * FROM combinedtab WHERE path=? ORDER BY path, mark DESC""", (path,path)):
            yield row

    def path_findroot(self, path):
        for row in self._conn.execute(r"""
                SELECT * FROM hashtab WHERE (path=:path) OR (substr(path,1,:path_len + 1) == :path || '/')
                UNION
                SELECT * FROM combinedtab WHERE (path=:path) OR (substr(path,1,:path_len + 1) == :path || '/')
            """, {'path':path, 'path_len':len(path)}):
            yield row



if __name__ == '__main__':
    from hashdb_config_base import CombineDB

    dbB = HashDatabase('test/secondary.db')
    dbB.open()

    dbC = HashDatabase('test/ternary.db')
    dbC.open()

    dbA = HashDatabase('test/primary.db')
    dbA.add_combine(CombineDB('', 'test/secondary.db', ''))
    dbA.add_combine(CombineDB('', 'test/ternary.db', ''))
    dbA.open()

    for row in dbA._conn.execute('SELECT * FROM combinedtab'):
        print row
